import xlwings as xw
from collections import defaultdict
from pathlib import Path
import numpy as np
import pandas as pd
import datetime
import pickle
from pathlib import Path
import xalpha as xa
from xalpha.cons import caldate
import click

xa.set_backend(defaultprev=365*20,backend="csv", path="./xalphadata")
class ConvBondLun():
    def __init__(self,start):
        # start day of analyze
        # 有备份文件优选使用备份文件。否则从excel读取。
        # 读取备份文件后按照日期更新到最新
        opendate = caldate.loc[(caldate['is_open']==1),:]
        opendate.loc[:,'date'] = pd.to_datetime(opendate.loc[:,'cal_date'])
        self.ref_dates = opendate.loc[(opendate['date']>=start)&(opendate['date']<=datetime.datetime.today()),'date']
        if Path("ConvBondLun.data").exists():
            self.readdata()
            # 检查是否需要更新
            if self.dates.values[-1] != self.ref_dates.values[-1]:
                # 需要更新
                for idate in self.ref_dates.loc[self.ref_dates>self.dates.values[-1]]:
                    tmppath = Path(f"参考榜单(投资有风险，买卖需谨慎)({idate.strftime('%Y%m%d')}).xlsx")
                    if not tmppath.exists():
                        print(f"{tmpath} 不存在，分析失败")
                        return 0
                # 开始更新
                self.app = xw.App(visible=True, add_book=False) # 程序可见，只打开不新建工作薄
                self.app.display_alerts = False # 警告关闭
                self.app.screen_updating = False # 屏幕更新关闭 
                for idate in self.ref_dates.loc[self.ref_dates>self.dates.values[-1]]:
                    tmppath = Path(f"参考榜单(投资有风险，买卖需谨慎)({idate.strftime('%Y%m%d')}).xlsx")
                    print(f"读取{idate}榜单：")
                    wb = self.app.books.open(tmppath)
                    for isheet in wb.sheets:
                        if isheet.name =='激进轮动可转债':
                            if isheet["M52"].value is None:
                                # only first 50 is given
                                therange = 'K2:N51'
                            else:
                                therange = 'K2:N101'
                            for item in isheet[therange].options(ndim=2).value:
                                icode = f"{int(item[0])}"
                                if icode in self.paiming.keys():
                                    self.paiming[icode]['rank'].append(int(item[2]))
                                    self.paiming[icode]['price'].append(item[3])
                                    self.paiming[icode]['date'].append(idate)
                                else:
                                    self.paiming[icode]['name'] = item[1]
                                    self.paiming[icode]['rank'] =[int(item[2])]
                                    self.paiming[icode]['price'] = [item[3]]
                                    self.paiming[icode]['date'] = [idate]
                            print("榜单读取完成")
                # 更新dates
                self.dates = self.ref_dates
        else:
#             self.dates = pd.bdate_range(start=start,end=datetime.datetime.now())
            self.dates = self.ref_dates
            # check if all file exists
            for idate in self.dates:
                tmppath = Path(f"参考榜单(投资有风险，买卖需谨慎)({idate.strftime('%Y%m%d')}).xlsx")
                if not tmppath.exists():
                    print(f"{tmpath} 不存在，分析失败")
                    return 0

            self.app = xw.App(visible=True, add_book=False) # 程序可见，只打开不新建工作薄
            self.app.display_alerts = False # 警告关闭
            self.app.screen_updating = False # 屏幕更新关闭    
            # key: code, value: dict
            # in which:
            # name:str, rank:list, price:list, date:list
            self.paiming = defaultdict(dict)
            for idate in self.dates:
                tmppath = Path(f"参考榜单(投资有风险，买卖需谨慎)({idate.strftime('%Y%m%d')}).xlsx")
                print(f"读取{idate}榜单：")
                wb = self.app.books.open(tmppath)
                for isheet in wb.sheets:
                    if isheet.name =='激进轮动可转债':
                        if isheet["M52"].value is None:
                            # only first 50 is given
                            therange = 'K2:N51'
                        else:
                            therange = 'K2:N101'
                        for item in isheet[therange].options(ndim=2).value:
                            icode = f"{int(item[0])}"
                            if icode in self.paiming.keys():
                                self.paiming[icode]['rank'].append(int(item[2]))
                                self.paiming[icode]['price'].append(item[3])
                                self.paiming[icode]['date'].append(idate)
                            else:
                                self.paiming[icode]['name'] = item[1]
                                self.paiming[icode]['rank'] =[int(item[2])]
                                self.paiming[icode]['price'] = [item[3]]
                                self.paiming[icode]['date'] = [idate]
                        print("榜单读取完成")
            self.app.quit()
    def savedata(self):
        # save data to pickle
        with open("ConvBondLun.data","wb") as f:
            pickle.dump(self.dates,f)
            pickle.dump(self.paiming,f)
        print("保存完成")
    def readdata(self):
        if Path("ConvBondLun.data").exists():
            with open("ConvBondLun.data","rb") as f:
                self.dates = pickle.load(f)
                self.paiming = pickle.load(f)
            print("读取完成")
        else:
            print("未找到ConvBondLun.data")
    
    def _get_rank_change(self,code):
        # get rank change with time for given code
        # return DataFrame:columns=date 	name 	rank 	price
        df1 = pd.DataFrame(self.paiming[code])
        if df1.shape[0]>0:
            df2 = pd.DataFrame(self.dates)
            df2.columns = ['date']
            df3 = df2.merge(df1,on='date',how='left')
            df3.loc[:,'rank'] = df3.loc[:,'rank'].fillna(999)
            df3.loc[:,'rank'] = df3.loc[:,'rank'].astype(int)
            df3.loc[:,'name'] = df3.loc[:,'name'].fillna(method='ffill')
            return df3
        else:
#             raise ValueError(f"{code}不在榜单中")
            df2 = pd.DataFrame(self.dates)
            df2.columns = ['date']
            df2.loc[:,'name'] = np.nan
            df2.loc[:,'rank'] = 999
            df2.loc[:,'price'] = np.nan
            return df2
    def _get_bangdan(self,date):
        # get bangdan on date.
        # date: str,'2021-12-10'
        # 恢复出某一天的榜单
        bangdan = []
        for ikey,ivalue in self.paiming.items():
            if 'date' in ivalue.keys():
                if pd.Timestamp(date) in ivalue['date']:
                    bangdan.append([ikey,ivalue['name'],ivalue['rank'][ivalue['date'].index(pd.Timestamp(date))],ivalue['price'][ivalue['date'].index(pd.Timestamp(date))]])
        if len(bangdan)>0:
            df = pd.DataFrame(bangdan,columns=['code','name','rank','price']).sort_values(by='rank')
            return df
        else:
            raise ValueError(f"{date}没有榜单")
    
    def analyze(self,codes,rank_lim=20,price_lim=170,keep_num=20):
        # codes: 用户持仓
        # rank_lim: 选择前多少名
        # price_lim: 筛选价格
        # 最近日期的持仓排名
        print(f"分析参数：筛选排名前{rank_lim}, 筛选价格 {price_lim}, 持仓个数 {keep_num}")
        bangdan = self._get_bangdan(self.dates.iloc[-1].strftime("%Y-%m-%d"))
        codes = np.array(codes)
        if len(codes.shape) == 1:
            df_code = pd.DataFrame(codes,columns=['code'])
            in_bangdan = bangdan.loc[bangdan['code'].isin(codes),:]
            onlycodes = codes
        else:
            df_code = pd.DataFrame(codes,columns=['code','name'])
            in_bangdan = bangdan.loc[bangdan['code'].isin(codes[:,0]),:]
            onlycodes = codes[:,0]
        
        chicang = df_code.merge(in_bangdan,on='code',how='left')
        chicang.loc[:,'rank'] = chicang.loc[:,'rank'].fillna(999)
        if 'name_x' in chicang.columns:
            chicang.loc[:,'name'] = chicang.loc[:,'name_x'].fillna('--')
        else:
            chicang.loc[:,'name'] = chicang.loc[:,'name'].fillna('--')
        chicang.loc[:,'price'] = chicang.loc[:,'price'].fillna('--')
        chicang = chicang.sort_values(by='rank')
        nkeep = 0
        print("{:<8}{:<12}{:<8}{:<8}{:<6}{:<6}{:<8}".format("代码","名称","最新排名","昨日价格","最小排名","最大排名","备注"))

        for ii,irow in chicang.iterrows():
            df4 =self._get_rank_change(irow['code'])
            ana = [int(i) for i in (df4['rank']<=rank_lim).values[-1::-1]]
            if ana[0] == 1:
                try:
                    nday = ana.index(0)
                except:
                    nday = len(self.dates)
                beizhu = f"已持续位于前{rank_lim}名{nday}天"
                nkeep += 1
            else:
                try:
                    nday = ana.index(1)
                except:
                    nday = len(self.dates)                
                beizhu = f"已于前{nday}天掉出前{rank_lim}名(推荐卖出)"
            print(f"{irow['code']:<8}{irow['name']:<12}{int(irow['rank']):<12}{irow['price']:<12}{df4['rank'].min():<12}{df4['rank'].max():<12}{beizhu:<8}")
#         print(nkeep)
        # 推荐
        print("===========================推荐买入：")
        print("{:<8}{:<12}{:<8}{:<8}{:<6}{:<6}{:<8}".format("代码","名称","最新排名","昨日价格","最小排名","最大排名","备注"))
        recom = bangdan.loc[(~bangdan['code'].isin(onlycodes))&(bangdan['price']<price_lim),:]
#         print(recom.shape)
        nrecom = 0
        for ii,irow in recom.iterrows():
            if nrecom< keep_num-nkeep:
                df4 =self._get_rank_change(irow['code'])
                ana = [int(i) for i in (df4['rank']<rank_lim).values[-1::-1]]
                try:
                    nday = ana.index(0)
                except:
                    nday = len(self.dates)
                beizhu = f"已持续位于前{rank_lim}名{nday}天"
                print(f"{irow['code']:<8}{irow['name']:<12}{int(irow['rank']):<12}{irow['price']:<12}{df4['rank'].min():<12}{df4['rank'].max():<12}{beizhu:<8}")
                nrecom += 1

def iszhuanzhai(row):
#     print(row['基金代码'][2:5])
    if row['基金代码'][2:5] in ['110','113','127','128','123']:
        return True
    else:
        return False

@click.group()
def check():
    pass

@check.command()
def shouyi():
    if Path('infield_record.csv').exists():
        ist=xa.irecord("infield_record.csv")
        mul = xa.imul(status=ist)
        print(f"汇总日期：{datetime.datetime.today()}")
        print(f"目前账户年化收益率 xirr={mul.xirrrate()*100}%\n")
        summary = mul.combsummary()
        print(summary.loc[summary['基金名称']=='总计',['基金现值','历史最大占用','基金持有成本','基金分红与赎回','换手率','基金收益总额','投资收益率']])

    else:
        print("找不到明细文件：infield_record.csv")

@check.command()
@click.option('--codes','-c',type=str,help='持仓文件，csv格式，包含代码和名称')
@click.option('--start','-s',type=str,help='榜单起始时间',default='2021-12-01')
@click.option('--ranklim','-rl',type=int,help='低于此名次将会轮出',default=20)
@click.option('--pricelim','-pl',type=float,help='筛选价格，低于此价格才能轮入',default=170.0)
@click.option('--keepnum','-kp',type=int,help='持仓个数，影响推荐个数',default=20)
def lundong(codes,start,ranklim,pricelim,keepnum):
    if codes is None:
        if Path('infield_record.csv').exists():
            ist=xa.irecord("infield_record.csv")
            mul = xa.imul(status=ist)
            print(f"汇总日期：{datetime.datetime.today()}")
            print(f"目前账户年化收益率 xirr={mul.xirrrate()*100}%\n")
            summary = mul.combsummary()
            print(summary.loc[summary['基金名称']=='总计',['基金现值','历史最大占用','基金持有成本','基金分红与赎回','换手率','基金收益总额','投资收益率']])

            df_cb = summary.loc[(summary['持有份额']>0)&(summary.apply(iszhuanzhai,axis=1)),:]
            cb_codesnames = []
            for ii,irow in df_cb.iterrows():
                cb_codesnames.append([irow['基金代码'][2:],irow['基金名称']])
        else:
            print("找不到明细文件：infield_record.csv")
    else:
        df_codesnames = pd.read_csv(codes,dtype=str)
        df_codesnames.columns=['code','name']
        cb_codesnames = df_codesnames.loc[:,['code','name']].values

    cb = ConvBondLun(start)
    cb.analyze(cb_codesnames,rank_lim=ranklim,price_lim=pricelim,keep_num=keepnum)
    cb.savedata()

@check.command()
@click.option('--code','-c',type=str,help='获取code的历史榜单排名',prompt='请输入要分析的转债代码',required=True)
@click.option('--start','-s',type=str,help='榜单起始时间',default='2021-12-01')
def rank(code,start):
    cb = ConvBondLun(start)
    print(cb._get_rank_change(code))



if __name__=='__main__':
    check()
